﻿#include "sqliteutil.h"
#include <QProgressDialog>
#include <QApplication>
#include <QDate>
#include <QMessageBox>
#include <QStandardPaths>
#include <QDir>


SqliteUtil::SqliteUtil()
{
    m_nRow = 0;
    m_nRowMax = 100;
    m_bDiscontinue = false;
}

SqliteUtil::~SqliteUtil()
{
    //关闭数据库
    disConnection();
    return;
}

void SqliteUtil::init(QString db)
{
    QSqlDatabase _sql;
    if(QSqlDatabase::contains(db)){
        _sql = QSqlDatabase::database(db);
        return;
    }

    m_DB = db;
    m_strDbPath = QDir::currentPath();
    m_strSqlPath = m_strDbPath;
    m_strDbPath += "/data/info.db";

    QFile fp;
    fp.setFileName(m_strDbPath);
    if (fp.exists())
    {
        createConnection(m_strDbPath);
    }
    else
    {
        fp.open(QIODevice::ReadWrite);
        fp.close();
        createConnection(m_strDbPath);
        excute_sql_file(m_strSqlPath);
    }
}

bool SqliteUtil::createConnection(QString dbname)
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", m_DB);
    if (!QSqlDatabase::drivers().contains("QSQLITE"))
        qInfo() << "drivers  Error!Unable to load databas!This App needs the SQLITE driver";
    db.setDatabaseName(dbname);
    QString strname = db.connectionName();
    if (!db.open()) {
        qInfo() << "open db Error!" << db.lastError();
        return false;
    }
    else {
        return true;
    }
}

int SqliteUtil::excute_sql_file(QString sql_file_path)
{
    int iRet = 0;

    sql_file_path = m_strSqlPath + "/data/table.sql";
    QFile qfile;
    qfile.setFileName(sql_file_path);
    if (!qfile.exists()) {
        return -1;
    }
    if (!qfile.open(QIODevice::ReadOnly | QIODevice::Text)) {
        return -1;
    }
    QTextStream in(&qfile);
    in.setCodec("UTF-8");
    QString qstr_file_data = in.readAll();
    QStringList qstrlist_sql = qstr_file_data.split(";");

    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery qsql(db);
    // 创建表

    for (int i = 0; i < qstrlist_sql.size() - 1; i++) {
        QString qstr_sql_part = qstrlist_sql.at(i).toUtf8();
        bool sql_result = qsql.exec(qstr_sql_part);
        if (!sql_result) {
            QSqlError sql_error = qsql.lastError();
            qInfo()<< sql_error;
            iRet = -1;
            break;
        }
    }

    // 创建触发器
    QStringList qstrTrigger_sql;
    qstrTrigger_sql.append(m_strSqlPath + "/data/change_TRIGGER.sql");
    qstrTrigger_sql.append(m_strSqlPath + "/data/default_TRIGGER.sql");

    for (int i = 0; i < qstrTrigger_sql.size(); i++) {
        QString qstr_Trigger = qstrTrigger_sql.at(i).toUtf8();
        qfile.setFileName(qstr_Trigger);
        QTextStream in(&qfile);
        if (!qfile.exists()) {
            return -1;
        }
        if (!qfile.open(QIODevice::ReadOnly | QIODevice::Text)) {
            return -1;
        }
        in.setCodec("UTF-8");
        QString qstr_data = in.readAll();
        bool sql_result = qsql.exec(qstr_data);
        if (!sql_result) {
            QSqlError sql_error = qsql.lastError();
            qInfo() << sql_error;
            iRet = -1;
            break;
        }
    }


    return iRet;
}

bool SqliteUtil::disConnection()
{
    {
        QSqlDatabase db = QSqlDatabase::database(m_DB);
        QSqlQuery query("SELECT NAME, DOB FROM EMPLOYEES", db);
    }
    // Both "db" and "query" are destroyed because they are out of scope
    QSqlDatabase::removeDatabase(m_DB); // correct
    return true;
}

bool SqliteUtil::createTabel(QString fileName)
{
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);
    QString tbname = QFileInfo(fileName).fileName();
    QStringList list = tbname.split(".");
    m_sTbname = list[0];
    strToSql(m_sTbname);
    file.setFileName(fileName);
    QString create_sql = QString("create table %1 (%2 int)").arg(m_sTbname).arg("js");
    if (!sql_query.exec(create_sql))
    {
        qInfo() << "Error: Fail to create table." << sql_query.lastError();
        return false;
    }
    else
    {
        qInfo() << "Table created!";
        return true;
    }
}

bool SqliteUtil::isExistTab(QString tbname)
{
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);

    QString fname = QFileInfo(tbname).fileName();
    QStringList list = fname.split(".");
    m_sTbname = list[0];
    strToSql(m_sTbname);
    QString strSql = QString("select count(*) from sqlite_master where name='%1'").arg(m_sTbname);

    if (!sql_query.exec(strSql))
    {
        return false;
    }
    else
    {
        while (sql_query.next())
        {
            int id = sql_query.value(0).toInt();
            if (id)
            {
                return true;
            }
            else {
                return false;
            }
        }
        return false;
    }
}


void SqliteUtil::strToSql(QString& str)
{
    str = str.replace(" ", "");
    str = str.replace("-", "0");
    str = str.replace("+", "0");
}

void SqliteUtil::deleteSingleData(int id, QString tbname)
{
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);
    QString strSql = QString("DELETE FROM %1 WHERE id = %2").arg(tbname).arg(id);
    if (!sql_query.exec(strSql))
    {
        qWarning() << sql_query.lastError();
    }
    else
    {
        qInfo() << "DELETE tbname deleteSingleData success";
    }
}

void SqliteUtil::deleteDefectschemes(int id)
{
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);
    sql_query.exec("PRAGMA foreign_keys = ON;");
    if (!sql_query.exec()) {
        qDebug() << "Failed to enable foreign keys:" << sql_query.lastError().text();
    }
    QString strSql = QString("DELETE FROM defect_schemes WHERE scheme_id = %1").arg(id);
    if (!sql_query.exec(strSql))
    {
        qWarning() << sql_query.lastError();
    }
    else
    {
        qInfo() << "DELETE tbname deleteSingleData success";
    }
}

QList<WoodProcessHistoryData> SqliteUtil::getWoodProcessHistoryData()
{
    QList<WoodProcessHistoryData> list;
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);
    sql_query.exec("select * from t_batch_lines;");
    if (!sql_query.exec())
    {
        qWarning() << sql_query.lastError();
    }
    else
    {
        while (sql_query.next())
        {
            WoodProcessHistoryData data;

            data.id = sql_query.value(0).toInt();
            data.detect_time = sql_query.value(15).toString();          // 加工时间
            data.wood_type = sql_query.value(12).toString();            // 种类
            data.wood_processing = sql_query.value(13).toString();      // 加工方式
            data.wood_ok_rate = sql_query.value(4).toDouble();          // 正常率
            data.wood_length = sql_query.value(5).toDouble();           // 长度
            data.wood_width = sql_query.value(6).toDouble();           // 宽度
            data.wood_area = sql_query.value(7).toDouble();            // 面积
            data.wood_dk = sql_query.value(8).toDouble();              // 死结率
            data.wood_lk = sql_query.value(9).toDouble();              // 活结率
            data.wood_ck = sql_query.value(10).toDouble();              // 裂纹
            data.wood_pith = sql_query.value(11).toDouble();            // 髓芯
            list.append(data);
        }
    }
    return list;
}

bool SqliteUtil::updateDefectScheme(int scheme_id, const DefectScheme &scheme)
{
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);

    sql_query.prepare(
        "UPDATE defect_schemes SET "
        "scheme_name = :name, "
        "head_end = :head_end, "
        "edge_size = :edge_size, "
        "is_unit = :is_unit, "
        "change_time = datetime('now','localtime') "
        "WHERE scheme_id = :id"
        );

    sql_query.bindValue(":name", scheme.schemeName);
    sql_query.bindValue(":head_end", scheme.headEnd);
    sql_query.bindValue(":edge_size", scheme.edgeSize);
    sql_query.bindValue(":is_unit", scheme.isUnit ? 1 : 0);
    sql_query.bindValue(":id", scheme_id);

    if (!sql_query.exec()) {
        qWarning() << "Update defect_schemes failed:" << sql_query.lastError();
        return false;
    }

    return sql_query.numRowsAffected() > 0;
}

bool SqliteUtil::updateDefectSettings(int scheme_id, const QList<DefectSetting> &settings) {
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    db.transaction();
    try {
        QSqlQuery selectQuery(db);
        selectQuery.prepare("SELECT sort_order FROM defect_settings WHERE scheme_id = ?");
        selectQuery.addBindValue(scheme_id);
        if (!selectQuery.exec()) {
            throw std::runtime_error(selectQuery.lastError().text().toStdString());
        }

        QSet<int> existingOrders;
        while (selectQuery.next()) {
            existingOrders.insert(selectQuery.value("sort_order").toInt());
        }

        for (const auto& setting : settings) {
            QSqlQuery upsertQuery(db);
            if (existingOrders.contains(setting.sortOrder)) {
                upsertQuery.prepare("UPDATE defect_settings SET "
                                    "defect_name = ?, is_active = ?, long_edge = ?, "
                                    "short_edge = ?, compensation = ?, open_together = ?, "
                                    "area_assist = ?, unacceptable = ?, edge_correction = ?, "
                                    "confidence = ? "
                                    "WHERE scheme_id = ? AND sort_order = ?");
                upsertQuery.addBindValue(setting.defectName);
                upsertQuery.addBindValue(setting.isActive);
                upsertQuery.addBindValue(setting.longEdge);
                upsertQuery.addBindValue(setting.shortEdge);
                upsertQuery.addBindValue(setting.compensation);
                upsertQuery.addBindValue(setting.openTogether);
                upsertQuery.addBindValue(setting.areaAssist);
                upsertQuery.addBindValue(setting.unacceptable);
                upsertQuery.addBindValue(setting.edgeCorrection);
                upsertQuery.addBindValue(setting.confidence);
                upsertQuery.addBindValue(scheme_id);
                upsertQuery.addBindValue(setting.sortOrder);
            } else {
                // 插入新记录（包含 sort_order）
                upsertQuery.prepare("INSERT INTO defect_settings ("
                                    "scheme_id, defect_name, sort_order, is_active, "
                                    "long_edge, short_edge, compensation, open_together, "
                                    "area_assist, unacceptable, edge_correction, confidence) "
                                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                upsertQuery.addBindValue(scheme_id);
                upsertQuery.addBindValue(setting.defectName);
                upsertQuery.addBindValue(setting.sortOrder);
                upsertQuery.addBindValue(setting.isActive);
                upsertQuery.addBindValue(setting.longEdge);
                upsertQuery.addBindValue(setting.shortEdge);
                upsertQuery.addBindValue(setting.compensation);
                upsertQuery.addBindValue(setting.openTogether);
                upsertQuery.addBindValue(setting.areaAssist);
                upsertQuery.addBindValue(setting.unacceptable);
                upsertQuery.addBindValue(setting.edgeCorrection);
                upsertQuery.addBindValue(setting.confidence);
            }
            if (!upsertQuery.exec()) {
                throw std::runtime_error(upsertQuery.lastError().text().toStdString());
            }
        }
        db.commit();
        return true;
    } catch (const std::exception& e) {
        db.rollback();
        qDebug() << "Update defect settings failed:" << e.what();
        return false;
    }
}


bool SqliteUtil::updateGeneralParams(int scheme_id, const QList<GeneralParam> &params) {
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    db.transaction();
    try {
        QSqlQuery selectQuery(db);
        selectQuery.prepare("SELECT sort_order FROM general_params WHERE scheme_id = ?");
        selectQuery.addBindValue(scheme_id);
        if (!selectQuery.exec()) {
            throw std::runtime_error(selectQuery.lastError().text().toStdString());
        }

        QSet<int> existingOrders;
        while (selectQuery.next()) {
            existingOrders.insert(selectQuery.value("sort_order").toInt());
        }

        for (const auto& param : params) {
            QSqlQuery upsertQuery(db);
            if (existingOrders.contains(param.sortOrder)) {
                upsertQuery.prepare("UPDATE general_params SET param_name = ?, param_value = ? WHERE scheme_id = ? AND sort_order = ?");
                upsertQuery.addBindValue(param.paramName);
                upsertQuery.addBindValue(param.paramValue);
                upsertQuery.addBindValue(scheme_id);
                upsertQuery.addBindValue(param.sortOrder);
            } else {
                upsertQuery.prepare("INSERT INTO general_params (scheme_id, param_name, param_value, sort_order) VALUES (?, ?, ?, ?)");
                upsertQuery.addBindValue(scheme_id);
                upsertQuery.addBindValue(param.paramName);
                upsertQuery.addBindValue(param.paramValue);
                upsertQuery.addBindValue(param.sortOrder);
            }
            if (!upsertQuery.exec()) {
                throw std::runtime_error(upsertQuery.lastError().text().toStdString());
            }
        }
        db.commit();
        return true;
    } catch (const std::exception& e) {
        db.rollback();
        qDebug() << "Update general parameters failed:" << e.what();
        return false;
    }
}

bool SqliteUtil::updateQualityGrades(int scheme_id, const QList<QualityGrade>& grades)
{
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    db.transaction();
    try {
        QSqlQuery selectQuery(db);
        selectQuery.prepare("SELECT sort_order FROM quality_grades WHERE scheme_id = ?");
        selectQuery.addBindValue(scheme_id);
        if (!selectQuery.exec()) {
            throw std::runtime_error(selectQuery.lastError().text().toStdString());
        }

        QSet<int> existingOrders;
        while (selectQuery.next()) {
            existingOrders.insert(selectQuery.value("sort_order").toInt());
        }

        for (const auto& grade : grades) {
            QSqlQuery upsertQuery(db);
            if (existingOrders.contains(grade.sortOrder)) {
                upsertQuery.prepare("UPDATE quality_grades SET grade = ?, weight = ? WHERE scheme_id = ? AND sort_order = ?");
                upsertQuery.addBindValue(grade.grade);
                upsertQuery.addBindValue(grade.weight);
                upsertQuery.addBindValue(scheme_id);
                upsertQuery.addBindValue(grade.sortOrder);
            } else {
                upsertQuery.prepare("INSERT INTO quality_grades (scheme_id, grade, weight, sort_order) VALUES (?, ?, ?, ?)");
                upsertQuery.addBindValue(scheme_id);
                upsertQuery.addBindValue(grade.grade);
                upsertQuery.addBindValue(grade.weight);
                upsertQuery.addBindValue(grade.sortOrder);
            }
            if (!upsertQuery.exec()) {
                throw std::runtime_error(upsertQuery.lastError().text().toStdString());
            }
        }
        db.commit();
        return true;
    } catch (const std::exception& e) {
        db.rollback();
        qDebug() << "Update quality grades failed:" << e.what();
        return false;
    }
}

bool SqliteUtil::updateEdgeParams(int scheme_id, const QList<EdgeParam>& params)
{
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    db.transaction();
    try {
        QSqlQuery selectQuery(db);
        selectQuery.prepare("SELECT sort_order FROM edge_params WHERE scheme_id = ?");
        selectQuery.addBindValue(scheme_id);
        if (!selectQuery.exec()) {
            throw std::runtime_error(selectQuery.lastError().text().toStdString());
        }

        QSet<int> existingOrders;
        while (selectQuery.next()) {
            existingOrders.insert(selectQuery.value("sort_order").toInt());
        }

        for (const auto& param : params) {
            QSqlQuery upsertQuery(db);
            if (existingOrders.contains(param.sortOrder)) {
                upsertQuery.prepare("UPDATE edge_params SET param_name = ?, param_value = ? WHERE scheme_id = ? AND sort_order = ?");
                upsertQuery.addBindValue(param.paramName);
                upsertQuery.addBindValue(param.paramValue);
                upsertQuery.addBindValue(scheme_id);
                upsertQuery.addBindValue(param.sortOrder);
            } else {
                upsertQuery.prepare("INSERT INTO edge_params (scheme_id, param_name, param_value, sort_order) VALUES (?, ?, ?, ?)");
                upsertQuery.addBindValue(scheme_id);
                upsertQuery.addBindValue(param.paramName);
                upsertQuery.addBindValue(param.paramValue);
                upsertQuery.addBindValue(param.sortOrder);
            }
            if (!upsertQuery.exec()) {
                throw std::runtime_error(upsertQuery.lastError().text().toStdString());
            }
        }
        db.commit();
        return true;
    } catch (const std::exception& e) {
        db.rollback();
        qDebug() << "Update edge parameters failed:" << e.what();
        return false;
    }
}

bool SqliteUtil::updateFixedLengths(int scheme_id, const QList<FixedLength>& lengths)
{
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    db.transaction();
    try {
        QSqlQuery selectQuery(db);
        selectQuery.prepare("SELECT sort_order FROM fixed_lengths WHERE scheme_id = ?");
        selectQuery.addBindValue(scheme_id);
        if (!selectQuery.exec()) {
            throw std::runtime_error(selectQuery.lastError().text().toStdString());
        }

        QSet<int> existingOrders;
        while (selectQuery.next()) {
            existingOrders.insert(selectQuery.value("sort_order").toInt());
        }

        for (const auto& length : lengths) {
            QSqlQuery upsertQuery(db);
            if (existingOrders.contains(length.sortOrder)) {
                upsertQuery.prepare("UPDATE fixed_lengths SET f_name = ?, is_active = ?, length = ? WHERE scheme_id = ? AND sort_order = ?");
                upsertQuery.addBindValue(length.fName);
                upsertQuery.addBindValue(length.isActive);
                upsertQuery.addBindValue(length.length);
                upsertQuery.addBindValue(scheme_id);
                upsertQuery.addBindValue(length.sortOrder);
            } else {
                upsertQuery.prepare("INSERT INTO fixed_lengths (scheme_id, f_name, is_active, length, sort_order) VALUES (?, ?, ?, ?, ?)");
                upsertQuery.addBindValue(scheme_id);
                upsertQuery.addBindValue(length.fName);
                upsertQuery.addBindValue(length.isActive);
                upsertQuery.addBindValue(length.length);
                upsertQuery.addBindValue(length.sortOrder);
            }
            if (!upsertQuery.exec()) {
                throw std::runtime_error(upsertQuery.lastError().text().toStdString());
            }
        }
        db.commit();
        return true;
    } catch (const std::exception& e) {
        db.rollback();
        qDebug() << "Update fixed lengths failed:" << e.what();
        return false;
    }
}

int SqliteUtil::insertDefectScheme(const DefectScheme &scheme)
{
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);
    int res = -1;

    sql_query.prepare(
        "INSERT INTO defect_schemes "
        "(scheme_name, head_end, edge_size, is_unit, create_time, change_time) "
        "VALUES (:name, :head_end, :edge_size, :is_unit, datetime('now','localtime'), datetime('now','localtime'))"
        );

    sql_query.bindValue(":name", scheme.schemeName);
    sql_query.bindValue(":head_end", scheme.headEnd);
    sql_query.bindValue(":edge_size", scheme.edgeSize);
    sql_query.bindValue(":is_unit", scheme.isUnit ? 1 : 0);

    if (!sql_query.exec()) {
        qWarning() << "Insert defect_schemes failed:" << sql_query.lastError();
    } else {
        res = sql_query.lastInsertId().toInt();
        qInfo() << "Inserted scheme with ID:" << res;
    }

    return res;
}

QList<DefectScheme> SqliteUtil::queryAllDefectSchemes()
{
    QList<DefectScheme> schemes;
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);
    sql_query.prepare(
        "SELECT scheme_id, scheme_name, head_end, edge_size, is_unit, "
        "create_time, change_time "
        "FROM defect_schemes "
        "ORDER BY create_time DESC"
        );

    if (!sql_query.exec()) {
        qWarning() << "Query error:" << sql_query.lastError();
        return schemes;
    }

    while (sql_query.next()) {
        DefectScheme scheme;
        scheme.schemeId = sql_query.value("scheme_id").toInt();
        scheme.schemeName = sql_query.value("scheme_name").toString();
        scheme.headEnd = sql_query.value("head_end").toDouble();
        scheme.edgeSize = sql_query.value("edge_size").toDouble();
        scheme.isUnit = sql_query.value("is_unit").toBool();
        scheme.createTime = sql_query.value("create_time").toString();
        scheme.changeTime = sql_query.value("change_time").toString();

        schemes.append(scheme);
    }
    return schemes;
}

bool SqliteUtil::queryDefectScheme(int scheme_id, DefectScheme &record)
{
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);
    sql_query.prepare(
        "SELECT scheme_id, scheme_name, head_end, edge_size, is_unit, "
        "create_time, change_time "
        "FROM defect_schemes "
        "WHERE scheme_id = :scheme_id"
        );
    sql_query.bindValue(":scheme_id", scheme_id);

    if (!sql_query.exec()) {
        qWarning() << "Query defect_schemes failed:" << sql_query.lastError();
        return false;
    }

    if (sql_query.next()) {
        record.schemeId = sql_query.value("scheme_id").toInt();
        record.schemeName = sql_query.value("scheme_name").toString();
        record.headEnd = sql_query.value("head_end").toDouble();
        record.edgeSize = sql_query.value("edge_size").toDouble();
        record.isUnit = sql_query.value("is_unit").toBool();
        record.createTime = sql_query.value("create_time").toString();
        record.changeTime = sql_query.value("change_time").toString();
    } else {
        qWarning() << "No scheme found with id:" << scheme_id;
        return false;
    }

    bool success = true;
    success &= queryDefectSettings(scheme_id, record.defectSettings);
    success &= queryGeneralParams(scheme_id, record.generalParams);
    success &= queryQualityGrades(scheme_id, record.qualityGrades);
    success &= queryEdgeParams(scheme_id, record.edgeParams);
    success &= queryFixedLengths(scheme_id, record.fixedLengths);

    return success;
}

bool SqliteUtil::queryDefectSettings(int scheme_id, QList<DefectSetting> &records)
{
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);

    sql_query.prepare(
        "SELECT id, scheme_id, defect_name, is_active, long_edge, short_edge, "
        "compensation, open_together, area_assist, unacceptable, "
        "edge_correction, confidence, sort_order, create_time, change_time "
        "FROM defect_settings "
        "WHERE scheme_id = :scheme_id "
        "ORDER BY sort_order"
        );
    sql_query.bindValue(":scheme_id", scheme_id);

    if (!sql_query.exec()) {
        qWarning() << "Query defect_settings failed:" << sql_query.lastError();
        return false;
    }

    while (sql_query.next()) {
        DefectSetting record;
        record.id = sql_query.value("id").toInt();
        record.schemeId = sql_query.value("scheme_id").toInt();
        record.defectName = sql_query.value("defect_name").toString();
        record.isActive = sql_query.value("is_active").toBool();
        record.longEdge = sql_query.value("long_edge").toDouble();
        record.shortEdge = sql_query.value("short_edge").toDouble();
        record.compensation = sql_query.value("compensation").toDouble();
        record.openTogether = sql_query.value("open_together").toBool();
        record.areaAssist = sql_query.value("area_assist").toBool();
        record.unacceptable = sql_query.value("unacceptable").toBool();
        record.edgeCorrection = sql_query.value("edge_correction").toDouble();
        record.confidence = sql_query.value("confidence").toDouble();
        record.sortOrder = sql_query.value("sort_order").toInt();
        record.createTime = sql_query.value("create_time").toString();
        record.changeTime = sql_query.value("change_time").toString();

        records.append(record);
    }
    return true;
}

bool SqliteUtil::queryGeneralParams(int scheme_id, QList<GeneralParam> &records)
{
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);

    sql_query.prepare(
        "SELECT id, scheme_id, param_name, param_value, sort_order, "
        "create_time, change_time "
        "FROM general_params "
        "WHERE scheme_id = :scheme_id "
        "ORDER BY sort_order"
        );
    sql_query.bindValue(":scheme_id", scheme_id);

    if (!sql_query.exec()) {
        qWarning() << sql_query.lastError();
        return false;
    }

    while (sql_query.next()) {
        GeneralParam record;
        record.id = sql_query.value("id").toInt();
        record.schemeId = sql_query.value("scheme_id").toInt();
        record.paramName = sql_query.value("param_name").toString();
        record.paramValue = sql_query.value("param_value").toString();
        record.sortOrder = sql_query.value("sort_order").toInt();
        record.createTime = sql_query.value("create_time").toString();
        record.changeTime = sql_query.value("change_time").toString();
        records.append(record);
    }

    return true;
}

bool SqliteUtil::queryQualityGrades(int scheme_id, QList<QualityGrade> &records)
{
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);

    sql_query.prepare(
        "SELECT id, scheme_id, grade, weight, sort_order, "
        "create_time, change_time "
        "FROM quality_grades "
        "WHERE scheme_id = :scheme_id "
        "ORDER BY sort_order"
        );
    sql_query.bindValue(":scheme_id", scheme_id);

    if (!sql_query.exec()) {
        qWarning() << "Query quality_grades failed:" << sql_query.lastError();
        return false;
    }

    while (sql_query.next()) {
        QualityGrade record;
        record.id = sql_query.value("id").toInt();
        record.schemeId = sql_query.value("scheme_id").toInt();
        record.grade = sql_query.value("grade").toString();
        record.weight = sql_query.value("weight").toDouble();
        record.sortOrder = sql_query.value("sort_order").toInt();
        record.createTime = sql_query.value("create_time").toString();
        record.changeTime = sql_query.value("change_time").toString();
        records.append(record);
    }

    return true;
}

bool SqliteUtil::queryEdgeParams(int scheme_id, QList<EdgeParam> &records)
{
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);

    sql_query.prepare(
        "SELECT id, scheme_id, param_name, param_value, sort_order, "
        "create_time, change_time "
        "FROM edge_params "
        "WHERE scheme_id = :scheme_id "
        "ORDER BY sort_order"
        );
    sql_query.bindValue(":scheme_id", scheme_id);

    if (!sql_query.exec()) {
        qWarning() << "Query edge_params failed:" << sql_query.lastError();
        return false;
    }

    while (sql_query.next()) {
        EdgeParam record;
        record.id = sql_query.value("id").toInt();
        record.schemeId = sql_query.value("scheme_id").toInt();
        record.paramName = sql_query.value("param_name").toString();
        record.paramValue = sql_query.value("param_value").toString();
        record.sortOrder = sql_query.value("sort_order").toInt();
        record.createTime = sql_query.value("create_time").toString();
        record.changeTime = sql_query.value("change_time").toString();
        records.append(record);
    }

    return true;
}

bool SqliteUtil::queryFixedLengths(int scheme_id, QList<FixedLength> &records)
{
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);

    sql_query.prepare(
        "SELECT id, scheme_id, f_name, is_active, length, sort_order, "
        "create_time, change_time "
        "FROM fixed_lengths "
        "WHERE scheme_id = :scheme_id "
        "ORDER BY sort_order"
        );
    sql_query.bindValue(":scheme_id", scheme_id);

    if (!sql_query.exec()) {
        qWarning() << "Query fixed_lengths failed:" << sql_query.lastError();
        return false;
    }

    while (sql_query.next()) {
        FixedLength record;
        record.id = sql_query.value("id").toInt();
        record.schemeId = sql_query.value("scheme_id").toInt();
        record.fName = sql_query.value("f_name").toString();
        record.isActive = sql_query.value("is_active").toBool();
        record.length = sql_query.value("length").toDouble();
        record.sortOrder = sql_query.value("sort_order").toInt();
        record.createTime = sql_query.value("create_time").toString();
        record.changeTime = sql_query.value("change_time").toString();
        records.append(record);
    }

    return true;
}

bool SqliteUtil::insertPlanLine(const T_PlanLine &planLine)
{
    QSqlDatabase db = QSqlDatabase::database(m_DB);

    if (!db.isOpen()) {
        qWarning() << "Database is not open!";
        return false;
    }

    QSqlQuery sql_query(db);
    sql_query.prepare("INSERT INTO t_plan_lines "
                  "(plan_name, scheme_id, scheme_name, scan_name, wood_type) "
                  "VALUES (:plan_name, :scheme_id, :scheme_name, :scan_name, :wood_type)");

    sql_query.bindValue(":plan_name", planLine.planName);
    sql_query.bindValue(":scheme_id", planLine.schemeId);
    sql_query.bindValue(":scheme_name", planLine.schemeName);
    sql_query.bindValue(":scan_name", planLine.scanName);
    sql_query.bindValue(":wood_type", planLine.woodType);

    if (!sql_query.exec()) {
        qWarning() << "Failed to insert plan line:" << sql_query.lastError().text();
        return false;
    }
    return true;
}

QList<T_PlanLine> SqliteUtil::queryAllPlanLines()
{
    QList<T_PlanLine> lines;
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);

    sql_query.prepare(
        "SELECT id, plan_name, scheme_id, scheme_name, scan_name, wood_type, create_time, change_time "
        "FROM t_plan_lines "
        "ORDER BY create_time DESC"
        );

    if (!sql_query.exec()) {
        qWarning() << "Query error:" << sql_query.lastError();
        return lines;
    }

    while (sql_query.next()) {
        T_PlanLine line;
        line.id = sql_query.value("id").toInt();
        line.planName = sql_query.value("plan_name").toString();
        line.schemeId = sql_query.value("scheme_id").toInt();
        line.schemeName = sql_query.value("scheme_name").toString();
        line.scanName = sql_query.value("scan_name").toString();
        line.woodType = sql_query.value("wood_type").toString();
        line.createTime = sql_query.value("create_time").toString();
        line.changeTime = sql_query.value("change_time").toString();

        lines.append(line);
    }

    return lines;
}



bool SqliteUtil::insertBatch(const T_Batch &batch) {
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);
    sql_query.prepare("INSERT INTO t_batch "
                  "(user_id, plan_id, wood_num, wood_ng_num, user_name, batch_name, plan_name) "
                  "VALUES (:user_id, :plan_id, :wood_num, :wood_ng_num, :user_name, :batch_name, :plan_name)");

    sql_query.bindValue(":user_id", batch.user_id);
    sql_query.bindValue(":plan_id", batch.plan_id);
    sql_query.bindValue(":wood_num", batch.wood_num);
    sql_query.bindValue(":wood_ng_num", batch.wood_ng_num);
    sql_query.bindValue(":user_name", batch.user_name);
    sql_query.bindValue(":batch_name", batch.batch_name);
    sql_query.bindValue(":plan_name", batch.plan_name);

    if (!sql_query.exec()) {
        qDebug() << "Insert failed:" << sql_query.lastError().text();
        return false;
    }
    return true;
}

QList<T_Batch> SqliteUtil::queryAllBatchs() {
    QList<T_Batch> batches;
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);
    sql_query.prepare("SELECT * FROM t_batch ORDER BY id DESC");


    if (!sql_query.exec()) {
        qDebug() << "Query failed:" << sql_query.lastError().text();
        return batches;
    }

    while (sql_query.next()) {
        T_Batch batch;
        batch.id          = sql_query.value("id").toInt();
        batch.user_id     = sql_query.value("user_id").toInt();
        batch.plan_id     = sql_query.value("plan_id").toInt();
        batch.wood_num    = sql_query.value("wood_num").toInt();
        batch.wood_ng_num = sql_query.value("wood_ng_num").toInt();
        batch.user_name   = sql_query.value("user_name").toString();
        batch.batch_name  = sql_query.value("batch_name").toString();
        batch.plan_name   = sql_query.value("plan_name").toString();
        batch.create_time = sql_query.value("create_time").toString();
        batch.change_time = sql_query.value("change_time").toString();
        batches.append(batch);
    }

    return batches;
}

T_PlanLine SqliteUtil::queryPlanLine(int plan_id) {
    T_PlanLine planLine;
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);
    sql_query.prepare("SELECT id, plan_name, scheme_id, scheme_name, scan_name, wood_type, "
                  "create_time, change_time FROM t_plan_lines WHERE id = :id");
    sql_query.bindValue(":id", plan_id);

    if (!sql_query.exec()) {
        qWarning() << "Failed to query plan line:" << sql_query.lastError();
        return planLine;
    }

    if (sql_query.next()) {
        planLine.id = sql_query.value("id").toInt();
        planLine.planName = sql_query.value("plan_name").toString();
        planLine.schemeId = sql_query.value("scheme_id").toInt();
        planLine.schemeName = sql_query.value("scheme_name").toString();
        planLine.scanName = sql_query.value("scan_name").toString();
        planLine.woodType = sql_query.value("wood_type").toString();
        planLine.createTime = sql_query.value("create_time").toString();
        planLine.changeTime = sql_query.value("change_time").toString();
    } else {
        qWarning() << "No plan line found with id:" << plan_id;
    }

    return planLine;
}

bool SqliteUtil::updatePlanLine(const T_PlanLine &line)
{
    if (line.id <= 0) {
        return false;
    }
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);
    sql_query.prepare("UPDATE t_plan_lines SET "
                  "plan_name = :plan_name, "
                  "scheme_id = :scheme_id, "
                  "scheme_name = :scheme_name, "
                  "scan_name = :scan_name, "
                  "wood_type = :wood_type, "
                  "change_time = datetime('now', 'localtime') "
                  "WHERE id = :id");

    sql_query.bindValue(":id", line.id);
    sql_query.bindValue(":plan_name", line.planName);
    sql_query.bindValue(":scheme_id", line.schemeId);
    sql_query.bindValue(":scheme_name", line.schemeName);
    sql_query.bindValue(":scan_name", line.scanName);
    sql_query.bindValue(":wood_type", line.woodType);

    if (!sql_query.exec()) {
        qDebug() << "Failed to update plan line:" << sql_query.lastError().text();
        return false;
    }

    return sql_query.numRowsAffected() > 0;
}

T_Batch SqliteUtil::queryBatchs(int batch_id) {
    T_Batch batch;
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);
    sql_query.prepare("SELECT * FROM t_batch WHERE id = :batch_id");
    sql_query.bindValue(":batch_id", batch_id);

    if (!sql_query.exec()) {
        qWarning() << "Query failed:" << sql_query.lastError().text();
        return batch;
    }

    if (sql_query.next()) {
        batch.id = sql_query.value("id").toInt();
        batch.user_id = sql_query.value("user_id").toInt();
        batch.plan_id = sql_query.value("plan_id").toInt();
        batch.wood_num = sql_query.value("wood_num").toInt();
        batch.wood_ng_num = sql_query.value("wood_ng_num").toInt();
        batch.user_name = sql_query.value("user_name").toString();
        batch.batch_name = sql_query.value("batch_name").toString();
        batch.plan_name = sql_query.value("plan_name").toString();
        batch.create_time = sql_query.value("create_time").toString();
        batch.change_time = sql_query.value("change_time").toString();
    } else {
        qDebug() << "not query batch id:" << batch_id;
    }

    return batch;
}

bool SqliteUtil::insertBatchLines(const QList<T_BatchLine> &batchLines) {
    QSqlDatabase db = QSqlDatabase::database(m_DB);

    if (!db.transaction()) {
        qDebug() << "Failed to start transaction:" << db.lastError().text();
        return false;
    }
    QSqlQuery sql_query(db);
    sql_query.prepare(
        "INSERT INTO t_batch_lines ("
        "  batch_id, wood_type, wood_processing, wood_ok_rate, wood_length, "
        "  wood_width, wood_line_area, wood_wood_area, wood_dk, wood_lk, "
        "  wood_cr, wood_hb, wood_em, wood_wh, wood_sm, "
        "  wood_bd, wood_pt, wood_rs, cam_img_json"
        ") VALUES ("
        "  :batch_id, :wood_type, :wood_processing, :wood_ok_rate, :wood_length, "
        "  :wood_width, :wood_line_area, :wood_wood_area, :wood_dk, :wood_lk, "
        "  :wood_cr, :wood_hb, :wood_em, :wood_wh, :wood_sm, "
        "  :wood_bd, :wood_pt, :wood_rs, :cam_img_json"
        ")"
        );

    for (const T_BatchLine &line : batchLines) {
        sql_query.bindValue(":batch_id", line.batch_id);
        sql_query.bindValue(":wood_type", line.wood_type);
        sql_query.bindValue(":wood_processing", line.wood_processing);
        sql_query.bindValue(":wood_ok_rate", line.wood_ok_rate);
        sql_query.bindValue(":wood_length", line.wood_length);
        sql_query.bindValue(":wood_width", line.wood_width);
        sql_query.bindValue(":wood_line_area", line.wood_line_area);
        sql_query.bindValue(":wood_wood_area", line.wood_wood_area);
        sql_query.bindValue(":wood_dk", line.wood_dk);
        sql_query.bindValue(":wood_lk", line.wood_lk);
        sql_query.bindValue(":wood_cr", line.wood_cr);
        sql_query.bindValue(":wood_hb", line.wood_hb);
        sql_query.bindValue(":wood_em", line.wood_em);
        sql_query.bindValue(":wood_wh", line.wood_wh);
        sql_query.bindValue(":wood_sm", line.wood_sm);
        sql_query.bindValue(":wood_bd", line.wood_bd);
        sql_query.bindValue(":wood_pt", line.wood_pt);
        sql_query.bindValue(":wood_rs", line.wood_rs);
        sql_query.bindValue(":cam_img_json", line.cam_img_json);

        if (!sql_query.exec()) {
            qDebug() << "Insert failed:" << sql_query.lastError().text()
            << "\nQuery:" << sql_query.lastQuery();
            db.rollback();
            return false;
        }
    }

    if (!db.commit()) {
        qDebug() << "Commit failed:" << db.lastError().text();
        db.rollback();
        return false;
    }

    return true;
}

QList<T_BatchLine> SqliteUtil::queryBatchLines(const int &batch_id)
{
    QList<T_BatchLine> batchLines;
    QSqlDatabase db = QSqlDatabase::database(m_DB);
    QSqlQuery sql_query(db);
    sql_query.prepare("SELECT * FROM t_batch_lines WHERE batch_id = :batch_id");
    sql_query.bindValue(":batch_id", batch_id);

    if (!sql_query.exec()) {
        qDebug() << "Query failed:" << sql_query.lastError().text();
        return batchLines;
    }

    while (sql_query.next()) {
        T_BatchLine line;
        line.id             = sql_query.value("id").toInt();
        line.batch_id       = sql_query.value("batch_id").toInt();
        line.wood_type      = sql_query.value("wood_type").toString();
        line.wood_processing= sql_query.value("wood_processing").toString();
        line.wood_ok_rate   = sql_query.value("wood_ok_rate").toDouble();
        line.wood_length    = sql_query.value("wood_length").toDouble();
        line.wood_width     = sql_query.value("wood_width").toDouble();
        line.wood_line_area = sql_query.value("wood_line_area").toDouble();
        line.wood_wood_area = sql_query.value("wood_wood_area").toDouble();
        line.wood_dk        = sql_query.value("wood_dk").toDouble();
        line.wood_lk        = sql_query.value("wood_lk").toDouble();
        line.wood_cr        = sql_query.value("wood_cr").toDouble();
        line.wood_hb        = sql_query.value("wood_hb").toDouble();
        line.wood_em        = sql_query.value("wood_em").toDouble();
        line.wood_wh        = sql_query.value("wood_wh").toDouble();
        line.wood_sm        = sql_query.value("wood_sm").toDouble();
        line.wood_bd        = sql_query.value("wood_bd").toDouble();
        line.wood_pt        = sql_query.value("wood_pt").toDouble();
        line.wood_rs        = sql_query.value("wood_rs").toDouble();
        line.cam_img_json   = sql_query.value("cam_img_json").toString();
        line.create_time    = sql_query.value("create_time").toString();
        line.change_time    = sql_query.value("change_time").toString();

        batchLines.append(line);
    }

    return batchLines;
}
